package com.kma.ncpractice2013.reports;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.*;
import java.sql.*;


import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;




/**
 * User: Viktor
 * Date: 10/13/13
 */
@WebServlet(name = "POIServlet")
public class POIServlet extends HttpServlet
{
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
        PrintWriter out = response.getWriter();
        Connection conn = null;
        try
        {
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("jdbc/security");
            conn = ds.getConnection();
            PreparedStatement ps = null;
            ResultSet resultSet = null;

            ps = conn.prepareStatement("SELECT * FROM USERS");
            resultSet = ps.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("sheet1");
            CellStyle style = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontName("Impact");
            font.setUnderline((byte) 1);
            font.setColor(IndexedColors.VIOLET.getIndex());
            style.setFont(font);
            style.setFillBackgroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
            style.setFillPattern(CellStyle.NO_FILL);
            XSSFRow row1 = sheet.createRow(0);
            for (int i = 1; i<=resultSetMetaData.getColumnCount(); i++)
            {
                XSSFCell cell = row1.createCell(i);
                cell.setCellValue(resultSetMetaData.getColumnName(i));
            }

            while (resultSet.next())
            {
                XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
                for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++)
                {
                    XSSFCell cell = row.createCell(i);

                    cell.setCellStyle(style);

                    cell.setCellValue(resultSet.getString(i));
                    sheet.autoSizeColumn(i);

                }

            }

            File file = new File("e:\\xlsxPOItest\\test.xlsx");
            File path = file.getParentFile();
            if (!path.exists() && !path.mkdirs())
            {
                throw new IllegalStateException("Couldn't create dir: " + path);
            }
            workbook.write(new FileOutputStream(file));

            out.print("great success");
        }
        catch (Exception e)
        {
            out.print(e.getMessage());
        } finally {
            try
            {
                conn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
            }

        }


    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}
